﻿Imports System.Data.OleDb

Public Class details
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        ' Hide adding comment, likes, adding tags, and rating from unregistred users
        If User.Identity.Name = "" Then
            Panel1.Visible = False
            Panel2.Visible = True
        End If

        ' set default value for title
        Dim dr As OleDbDataReader
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Try
            oleDbConn.Open()
            Dim TagExistCmd As OleDbCommand = New OleDbCommand("SELECT T_title FROM TBL_Topics Where T_ID=@F1", oleDbConn)
            TagExistCmd.Parameters.AddWithValue("@f1", Request.QueryString("D"))
            dr = TagExistCmd.ExecuteReader()
            dr.Read()
            TB_Title.Text = "Re : " & dr.GetValue(0)
        Catch ex As Exception
            TB_Title.Text = ""
        End Try


        Try
            Dim drlike As OleDbDataReader
            Dim LikeCmd As OleDbCommand = New OleDbCommand("SELECT 'OK' FROM TBL_TopicLikes Where topic_fkID=@f1 and UserName = @f2", oleDbConn)
            LikeCmd.Parameters.AddWithValue("@f1", Request.QueryString("D"))
            LikeCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
            drlike = LikeCmd.ExecuteReader()
            drlike.Read()
            If drlike.GetValue(0) <> "" Then
                img_Like.Visible = True
                Btn_like.Visible = False
            End If
        Catch ex As Exception
            img_Like.Visible = False
            Btn_like.Visible = True
        End Try

        ' log user activity - who viewed this topic... taken from https://eric.stca.herts.ac.uk/comqsjb/webscripting/
        If User.Identity.Name <> "" Then
            Try
                Dim ViewSQL As String = "INSERT INTO TBL_TopicViewed(Topic_fkID, UserName, view_date) VALUES(@f1,@f2,Now())"
                Dim ViewCommand As OleDbCommand = New OleDbCommand(ViewSQL, oleDbConn)
                ViewCommand.CommandType = CommandType.Text
                ViewCommand.Parameters.AddWithValue("@f1", Request.QueryString("D"))
                ViewCommand.Parameters.AddWithValue("@f2", User.Identity.Name)
                ViewCommand.ExecuteNonQuery()
            Catch ex As Exception
                ' probably there is duplicates, just ignore it.
            End Try
        End If

    End Sub

    Protected Sub btn_Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Submit.Click

        ' add the comments

        Dim sql As String = "INSERT INTO TBL_Comments (C_title, C_Text, C_Writer, C_Date, T_fkID) VALUES (@f1,@f2,@f3,NOW(),@f5)"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@f1", TB_Title.Text)
        cmd.Parameters.AddWithValue("@f2", TB_Text.Text)
        cmd.Parameters.AddWithValue("@f3", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f5", Request.QueryString("D"))
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()

        ChangeUserTitle()

        Response.Redirect("Details.aspx?D=" & Request.QueryString("D"))

    End Sub

    Protected Sub btn_Rate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Rate.Click

        ' if rating already exist then delete it
        Dim oleDbConn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        oleDbConn.Open()
        Dim deleteRatingSql As String = "DELETE FROM TBL_Topic_Rating WHERE T_fkID=@f1 AND Username=@f2"
        Dim deleteRatingCommand As New OleDbCommand(deleteRatingSql, oleDbConn)
        deleteRatingCommand.Parameters.AddWithValue("@f1", Request.QueryString("D"))
        deleteRatingCommand.Parameters.AddWithValue("@f2", User.Identity.Name)
        deleteRatingCommand.ExecuteNonQuery()

        ' Insert the rating
        Dim InsertRatingSql As String = "INSERT INTO TBL_Topic_Rating (T_fkID,Username,Rate) VALUES (@f1,@f2,@f3)"
        Dim InsertRatingCommand As New OleDbCommand(InsertRatingSql, oleDbConn)
        InsertRatingCommand.Parameters.AddWithValue("@f1", Request.QueryString("D"))
        InsertRatingCommand.Parameters.AddWithValue("@f2", User.Identity.Name)
        InsertRatingCommand.Parameters.AddWithValue("@f3", Rating1.CurrentRating)
        InsertRatingCommand.ExecuteNonQuery()
        lbl_Rating.Text = "Thank you for rating !!"
    End Sub

    Protected Sub Tag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Tag.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim Sql As String = "INSERT INTO TBL_TopicsTags (T_fkID, Tag_fkID) VALUES (@f1,@f2)"
        oleDbConn.Open()

        For Each cb As ListItem In CheckBoxList1.Items
            If cb.Selected Then
                Dim cmd As OleDbCommand = New OleDbCommand(Sql, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", Request.QueryString("D"))
                cmd.Parameters.AddWithValue("@f2", cb.Value)
                cmd.ExecuteNonQuery()
            End If
        Next

        If TextBox1.Text = "" Then Return

        Dim newTags = TextBox1.Text.Split(",")

        For Each newTag In newTags

            Dim TagExistId As Double
            Dim dr As OleDbDataReader

            Try

                Dim TagExistCmd As OleDbCommand = New OleDbCommand("SELECT Tag_ID FROM TBL_Tags Where Tag_Name=@F1", oleDbConn)
                TagExistCmd.Parameters.AddWithValue("@f1", newTag)
                dr = TagExistCmd.ExecuteReader()
                dr.Read()
                TagExistId = dr.GetValue(0)
            Catch ex As Exception
                TagExistId = 0
            End Try



            If TagExistId <= 0 Then
                ' inset that tag
                Dim newTagsql As String = "INSERT INTO TBL_Tags(Tag_Name) VALUES(@f1)"
                Dim NewTagcommand As OleDbCommand = New OleDbCommand(newTagsql, oleDbConn)
                NewTagcommand.CommandType = CommandType.Text
                NewTagcommand.Parameters.AddWithValue("@f1", newTag)
                NewTagcommand.ExecuteNonQuery()

                ' get the ID of last inserted tag

                Dim getlaslIdCmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 Tag_ID FROM TBL_Tags ORDER BY Tag_ID DESC", oleDbConn)
                Dim lastIdDR = getlaslIdCmd.ExecuteReader()
                lastIdDR.Read()
                TagExistId = lastIdDR.GetValue(0)
            End If

            'add it to the topic

            Dim TopicTagcmd As OleDbCommand = New OleDbCommand(Sql, oleDbConn)
            TopicTagcmd.CommandType = CommandType.Text
            TopicTagcmd.Parameters.AddWithValue("@f1", Request.QueryString("D"))
            TopicTagcmd.Parameters.AddWithValue("@f2", TagExistId)
            TopicTagcmd.ExecuteNonQuery()
        Next
        Response.Redirect("SearchByTag.aspx")

    End Sub

    Sub ChangeUserTitle()


        Dim SqlString As String
        Dim CommentCount As Long
        Dim TopicsCount As Long
        Dim ParticipationCount As Long
        Dim title As String

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)


        SqlString = "SELECT TopicsCount, CommentsCount FROM CountUserCommentTopicsSum where Writer = @1 "
        Dim ReadCmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        ReadCmd.CommandType = CommandType.Text
        oleDbConn.Open()
        ReadCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        Dim dr As OleDbDataReader = ReadCmd.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            If Not IsDBNull(dr("TopicsCount")) Then TopicsCount = dr("TopicsCount") Else TopicsCount = 0
            If Not IsDBNull(dr("CommentsCount")) Then CommentCount = dr("CommentsCount") Else CommentCount = 0
        End If

        ParticipationCount = TopicsCount + CommentCount

        title = ""
        If ParticipationCount < 100 Then
            title = "New Member"
        ElseIf ParticipationCount >= 100 And ParticipationCount < 200 Then
            title = "Member"
        ElseIf ParticipationCount >= 200 And ParticipationCount < 300 Then
            title = "Active Member"
        ElseIf ParticipationCount >= 300 Then
            title = "Senior Member"
        End If

        SqlString = "Update TBL_Profile set UserTitle=@f1 where UserName = @f2"
        Dim UpdateCmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)

        UpdateCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        UpdateCmd.Parameters.AddWithValue("@f2", title)

        UpdateCmd.ExecuteNonQuery()
        oleDbConn.Close()
        UpdateCmd.Dispose()
        oleDbConn.Dispose()

    End Sub


    Protected Sub Btn_like_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Btn_like.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        oleDbConn.Open()
        Dim LikeCmd As OleDbCommand = New OleDbCommand("Insert into TBL_TopicLikes(Topic_fkID, UserName, Liketime) values (@f1, @f2,Now()) ", oleDbConn)
        LikeCmd.Parameters.AddWithValue("@f1", Request.QueryString("D"))
        LikeCmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        LikeCmd.ExecuteNonQuery()

        img_Like.Visible = True
        Btn_like.Visible = False

        oleDbConn.Close()
        LikeCmd.Dispose()
        oleDbConn.Dispose()
    End Sub
End Class